﻿CREATE OR REPLACE FUNCTION reporte1(date, date)
  RETURNS SETOF record AS
$BODY$
declare
registro record;
begin
	FOR registro in SELECT Carga.idcarga,Cliente.idcliente, Cliente.nombre,Carga.fechaIngreso,Carga.fechaSalida,Carga.refrigerada, Carga.peso, Almacen.idalmacen, Rack.fila, Rack.columna, Rack.idrack 
	FROM Carga, Cliente, Rack, Almacen
	WHERE Carga.idcliente = Cliente.idCLiente and Carga.idRack = Rack.idRack
	and Rack.idalmacen = Almacen.idalmacen
	and (date(Carga.fechaIngreso) >= $1 and date(Carga.fechaIngreso)<=$2)
	ORDER BY Carga.fechaIngreso ASC
	Loop 
		Return Next registro;
	end Loop;
	Return;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION reporte1(date, date) OWNER TO postgres;


Select * from reporte1(cast('2011-01-01' as date), cast('2011-12-01' as date)) as (IdCarga integer, IdCliente integer, NombreCliente varchar(200), FechaIngreso timestamp, FechaSalida timestamp, Refrigerada boolean, PesoCarga numeric, IdAlmacen integer, Fila integer, Columna integer, IdRack integer);




CREATE OR REPLACE FUNCTION reporte2()
  RETURNS SETOF record AS
$BODY$
declare
registro record;
begin
	FOR registro IN SELECT Almacen.idalmacen, Almacen.estado, Rack.idrack, Rack.fila, Rack.columna, Carga.peso
	FROM Almacen, Rack, Carga
	WHERE Rack.idalmacen = Almacen.idalmacen and Carga.idrack = Rack.idrack
	ORDER BY Rack.idalmacen, Rack.fila, Rack.columna, rack.idrack
	LOOP
	  RETURN NEXT registro;
	END LOOP;
	RETURN;  
	end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION reporte2() OWNER TO postgres;

Select * from reporte2() as(idalmacen integer, estado boolean, idrack int, fila int, columna int, peso numeric);


Create or replace Function Reporte3 (integer) returns setof Record as $$
declare
registro record;
begin
	for registro in Select Carga.idcarga, Carga.fechaIngreso, Carga.fechaSalida, Servicio.idservicio, Servicio.tipo, Servicio.MontoServicio, Carga.Peso, Carga.MontoC
	FROM Carga, Servicio, Cliente 
	WHERE Cliente.idcliente = $1 and Carga.idcliente = Cliente.idcliente	
	ORDER BY Carga.fechaSalida ASC
	Loop 
	return next registro;
	end loop;
return;
end;
$$ LANGUAGE plpgSQL;

select * from reporte3(4) as (IDcarga integer, FechaIngreso timestamp, FechaSalida timestamp, IDservicio Integer,Tipo varchar(10),MontoServicio numeric, Peso numeric, MontoC numeric);


CREATE OR REPLACE FUNCTION reporte4(date, date)
  RETURNS SETOF record AS
$BODY$
declare
registro record;
begin
	FOR registro in SELECT Carga.idcarga, Carga.fechaIngreso,Carga.fechaSalida, Carga.peso, Almacen.idalmacen, Rack.fila, Rack.columna, Rack.idrack 
	FROM Carga, Rack, Almacen
	WHERE Carga.idRack = Rack.idRack and Almacen.idalmacen = Rack.idalmacen
	and (date(Carga.fechaSalida) >= $1 and date(Carga.fechaSalida)<=$2)
	ORDER BY Carga.fechaSalida ASC
	Loop 
		Return Next registro;
	end Loop;
	Return;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION reporte4(date, date) OWNER TO postgres;

Select * from reporte4(cast('2011-01-01' as date), cast('2011-12-01' as date)) as (IdCarga integer, FechaIngreso timestamp, FechaSalida timestamp, PesoCarga numeric, IdAlmacen integer, Fila integer, Columna integer, IdRack integer);



CREATE OR REPLACE FUNCTION reporte5(int) RETURNS SETOF record AS $$
declare
  registro record;
begin
  FOR registro IN SELECT to_char(fechaIngreso, 'month'),
  SUM(Carga.peso),
  SUM(CASE WHEN Carga.estado = true THEN peso ELSE 0 END),
  movimiento,
  SUM(montoc)
  FROM Carga
  WHERE EXTRACT (YEAR FROM (cast(fechaIngreso as date))) = $1 and EXTRACT (YEAR FROM (cast (fechaSalida as date)))=$1
  GROUP BY to_char(fechaIngreso, 'month'), movimiento, montoc
  LOOP
    RETURN NEXT registro;
  END LOOP;
  RETURN;
END;
$$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION reporte5(int) OWNER TO postgres;


Select * FROM reporte5(2011) as (mes text, cargatotalinresada numeric, ctotalegresada numeric, movimiento varchar, montoc numeric);

Create or replace Function Reporte6 () returns setof Record as $$
declare
registro record;
begin
	for registro in 
	Select Carga.idcarga, Almacen.idAlmacen, Rack.fila,Rack.columna, rack.idrack,carga.fechaSalida
	FROM Carga, Almacen, rack 
	WHERE Carga.idrack= Rack.idrack and Rack.Idalmacen = Almacen.Idalmacen and carga.estado = true	
	ORDER BY Carga.fechaSalida ASC
	Loop 
	return next registro;
	end loop;
return;
end;
$$ LANGUAGE plpgSQL;

select * from reporte6() as (IDcarga integer, IdAlmacen integer, fila integer, columna integer, IDrack integer, FechaSalida timestamp)
